<?php

namespace app\admin\controller;


use app\common\shop\OrderGoodsModel;
use think\Db;
use think\Request;
use app\common\entity\User;
use app\common\entity\UserInviteCode;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\IOFactory;
use app\common\entity\MywalletLog;
use app\common\entity\GoodsOrders;
use app\common\entity\AccountPos;

class Excelexport extends Admin
{




    #导出excel表
    protected  function excelExport($fileName = '', $headArr = [], $data = []) {

        $fileName       .= "_" . date("Y_m_d", time()) . ".xls";

        $spreadsheet    = new Spreadsheet();

        $objPHPExcel    = $spreadsheet->getActiveSheet();

        $key = ord("A"); // 设置表头

        foreach ($headArr as $v) {

            $colum = chr($key);

            $objPHPExcel->setCellValue($colum . '1', $v);

            $key += 1;

        }

        $column = 2;

        foreach ($data as $key => $rows) { // 行写入

            $span = ord("A");

            foreach ($rows as $keyName => $value) { // 列写入

                $objPHPExcel->setCellValue(chr($span) . $column, $value);

                $span++;

            }

            $column++;

        }

        //$fileName = iconv("utf-8", "gbk//IGNORE", $fileName); // 重命名表（UTF8编码不需要这一步）

        header('Content-Type: application/vnd.ms-excel');

        header('Content-Disposition: attachment;filename="' . $fileName . '.xlsx"');

        header('Cache-Control: max-age=0');

        $writer = new Xlsx($spreadsheet);

        $writer->save('php://output');

        //删除清空：

        $spreadsheet->disconnectWorksheets();

        unset($spreadsheet);

        exit;

    }




    public function user_list(){
        $headArr = ['ID','基本信息','用户等级','是否激活','机器编码','上级信息','福豆','福积分','活跃度','团队人数','直推人数','注册信息','状态'];
        $entity = User::field('u.*,c.invite_code')->alias('u');
        $codeTable = (new UserInviteCode())->getTable();
        $list = $entity->leftJoin("$codeTable c", 'u.id = c.user_id')
            ->field('c.invite_code,u.*,u.id as user_id ,w.fbb,w.fzf,w.act')
            ->leftJoin("my_wallet w", 'u.id = w.user_id')
            ->order('id desc')
            ->select();
        $userModel = new User();

        $data = [];
        foreach($list as $k=>$v){
            $childs1 = User::where('pid', $v['user_id'])->field('id')->select();
            $v['team_num'] = $userModel->getChildsNum($childs1);
            $v['zhitui'] = $userModel->where('pid', $v['user_id'])->count('id');
            $pInfo = User::where('id',$v['pid'])->field('nick_name,mobile')->find();
            $data[] = [
                $v['id'],
                "账号：".$v['nick_name']."\n手机：".$v['mobile']."\n真实姓名".$v['real_name']."\n邀请码".$v['invite_code'],
                User::getOneLevel($v['level']),
                $v['is_active'] == 1?'是':'否',
                $v['pos'],
                '账号：'.$pInfo['nick_name']."\n手机号：".$pInfo['mobile'],
                $v['fbb'],
                $v['fzf'],
                $v['act'],
                $v['team_num'],
                $v['zhitui'],
                $v['register_time']."\n".$v['register_ip'],
                $v['status'] == -1?'禁用':'正常'
            ];
        }
        $this->excelExport('用户列表',$headArr,$data);
    }

    #代理申请
    public function partnerrList(){
        $headArr = ['ID','用户账户','申请类型','地区','申请时间','状态','审核时间'];
        $entity = Db::name('partnerr')->alias('p')->join('user u ','u.id = p.user_id')->order('p.id desc')->field('p.*,u.mobile')->select();
        $data = [];
        foreach($entity as $k=>$v){
            $status = '';
            switch ($v['status']){
                case 1:$status = '待审核';break;
                case 2:$status = '通过';break;
                case 3:$status = '拒绝';break;
            }
            $data[] = [
                $v['id'],
                $v['mobile'],
                $v['type'] == 1?'申请市代理':'申请区代理',
                $v['province'].' '.$v['city'].' '.$v['area'],
                date('Y-m-d H:i:s',$v['create_time']),
                $status,
                date('Y-m-d H:i:s',$v['update_time']),
            ];
        }
        $this->excelExport('代理申请',$headArr,$data);
    }
    #合伙人列表
    public function partnerr(){
        $headArr = ['ID','用户账户','合伙人类型','地区'];
        $entity = \app\common\entity\User::alias('u')
            ->join('partnerr p','p.user_id = u.id')
            ->field('u.id,u.mobile,u.partner,u.partner2,p.type,p.province,p.city,p.area')
            ->where('partner2|partner',1)
            ->group('u.id')
            ->select();
        $data = [];
        foreach($entity as $k=>$v){
            $data[] = [
                $v['id'],
                $v['mobile'],
                $v['type'] == 1?'申请市代理':'申请区代理',
                $v['province'].' '.$v['city'].' '.$v['area'],
            ];
        }
        $this->excelExport('合伙人列表',$headArr,$data);
    }
    #交易列表
    public function marketList(){
        $headArr = ['ID','买入会员账户','买单类型','数量','价格','总价','卖出用户账号','状态','时间'];
        $userTable = (new \app\common\entity\User())->getTable();
        $entity = \app\common\entity\Market::alias('um')->field('um.*,u.nick_name,u.mobile,uu.nick_name as from_nick_name,uu.mobile as from_mobile')
            ->leftJoin("{$userTable} u", 'um.user_id = u.id')
            ->leftJoin('user uu', 'um.from_user_id = uu.id')
            ->order('um.create_time', 'desc')
            ->select();
        $data = [];
        foreach($entity as $k=>$v){
            $status = '';
            switch ($v['status']){
                case 1:$status = '等待匹配';break;
                case 2:$status = '等待付款';break;
                case 3:$status = '等待收款';break;
                case 4:$status = '交易完成';break;
                case 5:$status = '交易失败';break;
                case 6:$status = '取消交易';break;
                case 7:$status = '申诉中';break;
            }
            $matching_time = $v['matching_time']?date('Y-m-d H:i:s',$v['matching_time']):'';
            $pay_time = $v['pay_time']?date('Y-m-d H:i:s',$v['pay_time']):'';
            $end_time = $v['end_time']?date('Y-m-d H:i:s',$v['end_time']):'';
            $data[] = [
                $v['id'],
                $v['nick_name']?"账号:".$v['nick_name']."\n手机号:".$v['mobile']:'尚未匹配',
                $v['types'] == 1?'求购':'出售',
                $v['num'],
                $v['price'],
                $v['total'],
                $v['from_nick_name']?"账号:".$v['from_nick_name']."\n手机号:".$v['from_mobile']:'尚未匹配',
                $status,
                "创建:".$v['create_time']
                ."\n匹配:".$matching_time
                ."\n付款:".$pay_time
                ."\n完成:".$end_time,
            ];
        }
        $this->excelExport('交易列表',$headArr,$data);
    }
    #账变
    public function walletLogList(){
        $headArr = ['ID','会员账号','会员等级','变化数量','变化前','变化后','变化类型','钱包类型','时间'];
        $userTable = (new \app\common\entity\User())->getTable();
        $entity = MywalletLog::alias('um')->field('um.*,u.mobile,u.nick_name,u.level')
            ->leftJoin("{$userTable} u", 'um.user_id = u.id')
            ->order('um.create_time', 'desc')
            ->order('um.old', 'asc')
            ->select();
        $data = [];
        foreach($entity as $k=>$v){
            $data[] = [
                $v['id'],
                $v['mobile'],
                User::getOneLevel($v['level']),
                $v['number'],
                $v['old'],
                $v['new'],
                MywalletLog::getOneTypes($v['types']),
                MywalletLog::getChWalletName($v['money_type']),
                date('Y-m-d H:i:s',$v['create_time']),
            ];
        }
        $this->excelExport('账变列表',$headArr,$data);
    }
    #POS机编号
    public function snList(){
        $headArr = ['ID','机器编号','导入时间','绑定会员','是否绑定','绑定时间','会员名下'];
        $entity = Db::name('pos_sn')->order('id desc')->select();
        foreach ($entity as $k=>$item){
            if($item['is_bind'] == 1){
                $item['mobile'] = User::where('pos',$item['sn_id'])->value('mobile');
            }else{
                $item['mobile'] = '';
            }
            $account_pos = Db::name('account_pos')->where('sn_id',$item['sn_id'])->find();
            if($account_pos){
                $account_pos['mobile'] = User::where('id',$account_pos['user_id'])->value('mobile');
                $item['account_pos'] = $account_pos;
            }else{
                $item['account_pos'] = [];
            }
            $entity[$k] = $item;
        }
        $data = [];
        foreach($entity as $k=>$v){
            $data[] = [
                $v['id'],
                $v['sn_id'],
                date('Y-m-d H:i:s',$v['create_time']),
                $v['mobile'],
                $v['is_bind'] == 1?'已绑定':'未绑定',
                $v['bind_time']?date('Y-m-d H:i:s',$v['bind_time']):'',
                $v['mobile'] == 1?$v['mobile']:'暂无绑定',
            ];
        }
        $this->excelExport('POS机编号',$headArr,$data);
    }
    #名下机器
    public function account_pos(){
        $headArr = ['用户','总预安装','剩余预安装'];
        $entity = AccountPos::alias('p') ->join('user u','u.id = p.user_id')
            ->join('pos_sn s','s.sn_id = p.sn_id')
            ->field('u.preinstall,u.mobile,u.id as user_id,sum(s.is_bind) as is_bind')
            ->group('u.id');
        $data = [];
        foreach($entity as $k=>$v){
            $data[] = [
                $v['mobile'],
                $v['sum_number'],
                $v['preinstall'],
                $v['is_bind'],
            ];
        }
        $this->excelExport('名下机器列表',$headArr,$data);
    }
    #订单列表
    public function orderList(Request $request){
        $headArr = ['ID','订单号','买家账号	','商品名称	','商品规格	','数量	','付款金额	','收件人','联系手机号','地址','快递单号','订单时间','状态'];
        $where = [];
        $entity = Db::name('shop_order')->alias('o')->field('o.*,u.nick_name as user_nick_name');
        if($request->get('paytype')){
            $entity->where('o.paytype',$request->get('paytype'));
        }

        if($request->param('types')){
            $entity->where('o.status',$request->param('types'));
        }

        $list = $entity
            ->leftJoin("user u", 'u.id = o.uid')
            ->where($where)
            ->order('create_time desc')
            ->select();
        foreach ($list as $key => $value) {
            $goods = OrderGoodsModel::getOrderGoods($value);
            $list[$key]['goods'] = $goods[0];
            $list[$key]['count_goods'] = count( $goods);
            $address =  unserialize($value['address_date']);

            $list[$key]['address'] =$address;
        }

        $data = [];
        $model = new GoodsOrders();
        foreach($list as $k=>$v){
            $send_time = $v['sendtime']?date('Y-m-d H:i:s',$v['sendtime']):'';
            $finish_time = $v['finishtime']?date('Y-m-d H:i:s',$v['finishtime']):'';
            $data[] = [
                $v['id']."\t",
                $v['ordersn']."\t",
                $v['user_nick_name']."\t",
                $v['goods']['title']."\t",
                $v['goods']['spectitle']."\t",
                $v['goods']['number']."\t",
                $v['total']."\t",
                $v['address']['name']."\t",
                $v['address']['mobile']."\t",
                $v['address']['province'].$v['address']['city'].$v['address']['area'].$v['address']['address']."\t",
                $v['expressnumber']."\t",
                "下单:".$v['createtime']."\n发货时间:".$send_time."\n完成时间:".$finish_time."\t",
                $model->getStatusName($v['status'])
            ];
        }
        $this->excelExport('订单列表',$headArr,$data);
    }
}
